﻿Imports System.Data.SqlServerCe
Imports System.Data
Imports Ocean.OceanFramework.SQLDataAccess

Namespace SQLCompactDataAccess

    Public Class DataAccess

        Private Sub LoadCommandParameters(ByRef cmd As SqlCeCommand, ByVal ParamArray Parameters() As SqlCeParameter)

            If Parameters IsNot Nothing Then

                For Each p As SqlCeParameter In Parameters
                    cmd.Parameters.Add(p)
                Next

            End If

        End Sub

#Region " Declarations "

        Private _objSQLCeConnection As SqlCeConnection
        Private _strConnectionString As String = String.Empty

#End Region

#Region " Properties "

        ''' <summary>
        ''' Gets the current SQL Connection.
        ''' </summary>
        ''' <returns>SQLConnection</returns>
        Public ReadOnly Property SQLCeConnection() As SqlCeConnection
            Get
                Return _objSQLCeConnection
            End Get
        End Property

#End Region

#Region " Constructors "

        ''' <summary>
        ''' Creates a new instance of the DataAccess class utilizing the connection string passed as a parameter
        ''' </summary>
        ''' <param name="strConnectionString">SQL Client connection string</param>
        Public Sub New(ByVal strConnectionString As String)
            _strConnectionString = strConnectionString
            _objSQLCeConnection = New SqlCeConnection(_strConnectionString)
        End Sub

        ''' <summary>
        ''' Creates a new instance of the DataAccess class utilizing the connection passed as a parameter
        ''' </summary>
        ''' <param name="objSQLCeConnection">SQL Client Connection</param>
        Public Sub New(ByVal objSQLCeConnection As SqlCeConnection)
            _objSQLCeConnection = objSQLCeConnection
        End Sub

#End Region

#Region " ExecuteNonQuery "

        ''' <summary>
        ''' Executes a sql command using the supplied parameters and returns the number of rows affected
        ''' </summary>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>The number of rows affected</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        ''' <remarks>
        ''' <para>You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements</para>
        ''' <para>Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data</para> 
        ''' <para>For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1</para> 
        ''' </remarks>
        Public Function ExecuteNonQuery(ByVal CommandText As String, ByVal ParamArray Parameters() As SqlCeParameter) As Integer

            If Me.SQLCeConnection.State <> ConnectionState.Open AndAlso Me.SQLCeConnection.State <> ConnectionState.Closed Then
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "ExecuteNonQuery", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLCeConnection.State.ToString))
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "ExecuteNonQuery", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            If Me.SQLCeConnection.State = ConnectionState.Open Then
                'connection is being controlled by the calling procedure
                Using cmd As New SqlCeCommand(CommandText, Me.SQLCeConnection)
                    LoadCommandParameters(cmd, Parameters)
                    Return cmd.ExecuteNonQuery()
                End Using

            Else
                'connection will be controlled by this procedure, ie. connection state is closed
                Using Me.SQLCeConnection
                    Using cmd As New SqlCeCommand(CommandText, Me.SQLCeConnection)
                        LoadCommandParameters(cmd, Parameters)
                        Me.SQLCeConnection.Open()
                        Return cmd.ExecuteNonQuery()
                    End Using
                End Using
            End If

        End Function

#End Region

#Region " ExecuteScaler "

        ''' <summary>
        ''' Executes a sql ExecuteScaler command using the supplied parameters and returns the value
        ''' </summary>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>The number of rows affected</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        Public Function ExecuteScaler(ByVal CommandText As String, ByVal ParamArray Parameters() As SqlCeParameter) As Object

            If Me.SQLCeConnection.State <> ConnectionState.Open AndAlso Me.SQLCeConnection.State <> ConnectionState.Closed Then
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "ExecuteScaler", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLCeConnection.State.ToString))
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "ExecuteScaler", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            If Me.SQLCeConnection.State = ConnectionState.Open Then
                'connection is being controlled by the calling procedure
                Using cmd As New SqlCeCommand(CommandText, Me.SQLCeConnection)
                    LoadCommandParameters(cmd, Parameters)
                    Return cmd.ExecuteScalar
                End Using

            Else
                'connection will be controlled by this procedure, ie. connection state is closed
                Using Me.SQLCeConnection
                    Using cmd As New SqlCeCommand(CommandText, Me.SQLCeConnection)
                        LoadCommandParameters(cmd, Parameters)
                        Me.SQLCeConnection.Open()
                        Return cmd.ExecuteScalar
                    End Using
                End Using
            End If

        End Function

#End Region

#Region " GetDataReader "

        ''' <summary>
        ''' Executes a sql command to return a SQL Client DataReader object.  The developer MUST close the DataReader and Connection object in the calling code when done with the reader.
        ''' </summary>
        ''' <param name="CommandText">Command text, normally this is the stored procedure name</param>
        ''' <param name="CommandBehavior">Command behaviors should be selected to get best performance.  The close connection behavior should be selected to ensure that the connection is closed when the DataReader is closed</param>
        ''' <param name="Parameters">Param array of SQL Parameter objects.  For inbound parameters you must set the value of the parameter</param>
        ''' <returns>SQLCeDataReader object</returns>
        ''' <exception cref="DataAccessException">
        ''' <para>Thrown when <paramref name="cn" /><c>Connection State not equal to Open or Closed</c></para>
        ''' <para>Thrown when <paramref name="CommandText" /><c>Nothing or not assigned</c></para>
        ''' </exception>
        Public Function GetDataReader(ByVal CommandText As String, ByVal CommandBehavior As CommandBehavior, ByVal ParamArray Parameters() As SqlCeParameter) As SqlCeDataReader

            If Me.SQLCeConnection.State <> ConnectionState.Open AndAlso Me.SQLCeConnection.State <> ConnectionState.Closed Then
                Throw New DataAccessException(DatabaseReturnCode.ConnectionStateError, "GetDataReader", String.Format("The sqlconnection state was {0}.  The method only permits the sqlconnection state to be either open or closed only.", Me.SQLCeConnection.State.ToString))
            End If

            If String.IsNullOrEmpty(CommandText) Then
                Throw New DataAccessException(DatabaseReturnCode.MissingCommandText, "GetDataReader", "The method did not receive any command text.  Calling code must supply the command text.")
            End If

            Dim cmd As SqlCeCommand = Nothing
            cmd = New SqlCeCommand(CommandText, Me.SQLCeConnection)
            LoadCommandParameters(cmd, Parameters)

            If Me.SQLCeConnection.State = ConnectionState.Closed Then
                Me.SQLCeConnection.Open()
            End If

            Return cmd.ExecuteReader(CommandBehavior)
        End Function

#End Region

    End Class

End Namespace
